'''数据库连接文件'''
import pymssql

server = '172.16.3.19'
user = 'sa'
password = '*#zw8'
database = 'TOP_ERP_WHUT'

try:

    # 打开数据库连接（ip/数据库用户名/登录密码/数据库名）
    db = pymssql.connect(server=server, user=user, password=password, database=database)
    print("数据库连接成功")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
except pymssql.Error as e:
    print("数据库连接失败：" + str(e))

# 课程数：63
def init_course():
    course_info = []
    sql = ''' select distinct CourseID from VB_Score_Manage where VB_Score_Manage.Year = '2021-2022' and VB_Score_Manage.Term = '1' and VB_Score_Manage.WeekStart is not null and 
    VB_Score_Manage.CourseID not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
    '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
    '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
    '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
    '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
     '01714006','00111001') and VB_Score_Manage.Deptid='001' '''
    try:
        cursor.execute(sql)
        results_cou = cursor.fetchall()
        for cou in results_cou:
            course_info.append(cou)
    except Exception as e:
        print(e)
    return course_info

# 学生数：942
def init_students():
    stu_info = []
    sql = '''select distinct GraduateID from VB_SelectCourse_Base where year='2021-2022' and term='1' and Sno in (select Sno from VB_Score_Manage where VB_Score_Manage.Year = '2021-2022' and VB_Score_Manage.Term = '1' and VB_Score_Manage.WeekStart is not null and 
    VB_Score_Manage.CourseID not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
    '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
    '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
    '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
    '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
     '01714006','00111001') and VB_Score_Manage.Deptid='001') '''
    try:
        cursor.execute(sql)
        results_stu = cursor.fetchall()
        for stu in results_stu:
            stu_info.append(stu)
    except Exception as e:
        print(e)
    return stu_info

# 老师数量：58
def init_teachers():
    tea_info = []
    sql = ''' select distinct Teacher from VB_Score_Manage where VB_Score_Manage.Year = '2021-2022' and VB_Score_Manage.Term = '1' and VB_Score_Manage.WeekStart is not null and 
    VB_Score_Manage.CourseID not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
    '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
    '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
    '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
    '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
     '01714006','00111001') and VB_Score_Manage.Deptid='001' '''
    try:
        cursor.execute(sql)
        results_tea = cursor.fetchall()
        for tea in results_tea:
            tea_info.append(tea)
    except Exception as e:
        print(e)
    return tea_info

# 教室数量：974
def init_classrooms():
    classroom_info = []
    sql = "select ClassRoomID,PeopleNumber,ClassTypeName from VB_ClassRoom where ClassTypeName in ( '实验室','多媒体','普通教室','智慧教室')"
    try:
        cursor.execute(sql)
        results_clr = cursor.fetchall()
        for clr in results_clr:
            classroom_info.append(clr)  # 将教室信息添加到classroom_info列表中
            # classroom_info = [[26，150][74,40],[2,80].......]
    except Exception as e:
        print(e)
    return classroom_info

# 教学任务：70， 老师-课程： 65
def init_teaching_assignment():
    teaching_assignment = []
    sql =''' select Teacher,CourseID from VB_Score_Manage where VB_Score_Manage.Year = '2021-2022' and VB_Score_Manage.Term = '1' and VB_Score_Manage.WeekStart is not null and 
    VB_Score_Manage.CourseID not in('01544003','01524003','01524002','02614002','02614003','00114002','01214002','00414003','00214002','00214003','00514002','00514003','00114002','00414003','01314003','00914001',
    '00914002','00914003','01214001','01314003','01414002','01414001','01414002','01314001','01214003','01414003','00314005','01014003','00314005','00314006','01014001','01014003','00714001',
    '00314006','01114003','01114003','01114002','01114003','02624002','02624001','02624003','00124002','00162001','07024003','07024002','07024001','01024006','01024005','00324007',
    '00324006','05524001','00724004','01124002','01124003','01524301','01424004','01424003','0124002','01424005','01424002','00744001','00744002','00144001','00144003','00344015','00944003',
    '01944319','02141106','01544002','02644008','00614002L','00314002','01324901','00114003','00344902','00344801','00344013','00344012','00344014','01614001','01614002','00614003','00614001','01711410',
     '01714006','00111001') and VB_Score_Manage.Deptid='001' '''
    try:
        cursor.execute(sql)
        results_clr = cursor.fetchall()
        for clr in results_clr:
            teaching_assignment.append(clr)  # 将教学任务添加到teaching_assignment列表中
    except Exception as e:
        print(e)
    return teaching_assignment